using System;
using System.Data;
using Utils;
using Utils.DatabaseUtils;
using System.Data.SqlClient;
using System.Collections;


namespace QLCG.DAO
{
public class tblChuyenGiaUI
{ 
	private int idChuyenGia;
	public int IdChuyenGia
	{
		get { return idChuyenGia;}
		set { idChuyenGia = value;}
	}

	private String hoTenChuyenGia;
	public String HoTenChuyenGia
	{
		get { return hoTenChuyenGia;}
		set { hoTenChuyenGia = value;}
	}

	private int phieuSo;
	public int PhieuSo
	{
		get { return phieuSo;}
		set { phieuSo = value;}
	}

	private DateTime ngayDangKy;
	public DateTime NgayDangKy
	{
		get { return ngayDangKy;}
		set { ngayDangKy = value;}
	}

	private bool gioiTinh;
	public bool GioiTinh
	{
		get { return gioiTinh;}
		set { gioiTinh = value;}
	}

	private String coQuan_TenChinhThuc;
	public String CoQuan_TenChinhThuc
	{
		get { return coQuan_TenChinhThuc;}
		set { coQuan_TenChinhThuc = value;}
	}

	private String coQuan_TenGiaoDich;
	public String CoQuan_TenGiaoDich
	{
		get { return coQuan_TenGiaoDich;}
		set { coQuan_TenGiaoDich = value;}
	}

	private String coQuan_DiaChi;
	public String CoQuan_DiaChi
	{
		get { return coQuan_DiaChi;}
		set { coQuan_DiaChi = value;}
	}

	private String coQuan_Tinh;
	public String CoQuan_Tinh
	{
		get { return coQuan_Tinh;}
		set { coQuan_Tinh = value;}
	}

	private String coQuan_DienThoai;
	public String CoQuan_DienThoai
	{
		get { return coQuan_DienThoai;}
		set { coQuan_DienThoai = value;}
	}

	private String coQuan_Fax;
	public String CoQuan_Fax
	{
		get { return coQuan_Fax;}
		set { coQuan_Fax = value;}
	}

	private String coQuan_Email;
	public String CoQuan_Email
	{
		get { return coQuan_Email;}
		set { coQuan_Email = value;}
	}

	private String nhaRieng_DiaChi;
	public String NhaRieng_DiaChi
	{
		get { return nhaRieng_DiaChi;}
		set { nhaRieng_DiaChi = value;}
	}

	private String nhaRieng_DienThoai;
	public String NhaRieng_DienThoai
	{
		get { return nhaRieng_DienThoai;}
		set { nhaRieng_DienThoai = value;}
	}

	private String nhaRieng_Email;
	public String NhaRieng_Email
	{
		get { return nhaRieng_Email;}
		set { nhaRieng_Email = value;}
	}

	private DateTime ngaySinh;
	public DateTime NgaySinh
	{
		get { return ngaySinh;}
		set { ngaySinh = value;}
	}

	private String noiSinh;
	public String NoiSinh
	{
		get { return noiSinh;}
		set { noiSinh = value;}
	}

	private String hocHam;
	public String HocHam
	{
		get { return hocHam;}
		set { hocHam = value;}
	}

	private String daoTao;
	public String DaoTao
	{
		get { return daoTao;}
		set { daoTao = value;}
	}

	private String hiepHoi;
	public String HiepHoi
	{
		get { return hiepHoi;}
		set { hiepHoi = value;}
	}

	private String anPham;
	public String AnPham
	{
		get { return anPham;}
		set { anPham = value;}
	}

	private byte cachThamGia;
	public byte CachThamGia
	{
		get { return cachThamGia;}
		set { cachThamGia = value;}
	}

	public tblChuyenGiaUI() {}

	public tblChuyenGiaUI(int idChuyenGia, String hoTenChuyenGia, int phieuSo, DateTime ngayDangKy, bool gioiTinh, String coQuan_TenChinhThuc, String coQuan_TenGiaoDich, String coQuan_DiaChi, String coQuan_Tinh, String coQuan_DienThoai, String coQuan_Fax, String coQuan_Email, String nhaRieng_DiaChi, String nhaRieng_DienThoai, String nhaRieng_Email, DateTime ngaySinh, String noiSinh, String hocHam, String daoTao, String hiepHoi, String anPham, byte cachThamGia)
	 {
		this.idChuyenGia = idChuyenGia;
 		this.hoTenChuyenGia = hoTenChuyenGia;
 		this.phieuSo = phieuSo;
 		this.ngayDangKy = ngayDangKy;
 		this.gioiTinh = gioiTinh;
 		this.coQuan_TenChinhThuc = coQuan_TenChinhThuc;
 		this.coQuan_TenGiaoDich = coQuan_TenGiaoDich;
 		this.coQuan_DiaChi = coQuan_DiaChi;
 		this.coQuan_Tinh = coQuan_Tinh;
 		this.coQuan_DienThoai = coQuan_DienThoai;
 		this.coQuan_Fax = coQuan_Fax;
 		this.coQuan_Email = coQuan_Email;
 		this.nhaRieng_DiaChi = nhaRieng_DiaChi;
 		this.nhaRieng_DienThoai = nhaRieng_DienThoai;
 		this.nhaRieng_Email = nhaRieng_Email;
 		this.ngaySinh = ngaySinh;
 		this.noiSinh = noiSinh;
 		this.hocHam = hocHam;
 		this.daoTao = daoTao;
 		this.hiepHoi = hiepHoi;
 		this.anPham = anPham;
 		this.cachThamGia = cachThamGia;
 	}

	public int Add(String hoTenChuyenGia, int phieuSo, DateTime ngayDangKy, bool gioiTinh, String coQuan_TenChinhThuc, String coQuan_TenGiaoDich, 
        String coQuan_DiaChi, String coQuan_Tinh, String coQuan_DienThoai, String coQuan_Fax, String coQuan_Email, String nhaRieng_DiaChi, 
        String nhaRieng_DienThoai, String nhaRieng_Email, DateTime ngaySinh, String noiSinh, String hocHam, String daoTao, String hiepHoi, 
        String anPham, byte cachThamGia, ArrayList listChuyenMon, ArrayList listCongViec, ArrayList listLinhVuc, ArrayList listTieuLinhVuc,
        DataTable listDuAn, DataTable listHocVi, DataTable listNgoaiNgu, DataTable listCongTac)
	{
        SqlConnection conn = null;
        SqlTransaction trans = null;
        try
        {
            conn = new SqlConnection(Common.Conn);
            conn.Open();
            trans = conn.BeginTransaction();

            SqlCommand cmd = new SqlCommand("tblChuyenGia_Add", conn, trans);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@HoTenChuyenGia", hoTenChuyenGia);
            cmd.Parameters.AddWithValue("@PhieuSo", phieuSo);
            cmd.Parameters.AddWithValue("@NgayDangKy", ngayDangKy);
            cmd.Parameters.AddWithValue("@GioiTinh", gioiTinh);
            cmd.Parameters.AddWithValue("@CoQuan_TenChinhThuc", coQuan_TenChinhThuc);
            cmd.Parameters.AddWithValue("@CoQuan_TenGiaoDich", coQuan_TenGiaoDich);
            cmd.Parameters.AddWithValue("@CoQuan_DiaChi", coQuan_DiaChi);
            cmd.Parameters.AddWithValue("@CoQuan_Tinh", coQuan_Tinh);
            cmd.Parameters.AddWithValue("@CoQuan_DienThoai", coQuan_DienThoai);
            cmd.Parameters.AddWithValue("@CoQuan_Fax", coQuan_Fax);
            cmd.Parameters.AddWithValue("@CoQuan_Email", coQuan_Email);
            cmd.Parameters.AddWithValue("@NhaRieng_DiaChi", nhaRieng_DiaChi);
            cmd.Parameters.AddWithValue("@NhaRieng_DienThoai", nhaRieng_DienThoai);
            cmd.Parameters.AddWithValue("@NhaRieng_Email", nhaRieng_Email);
            cmd.Parameters.AddWithValue("@NgaySinh", ngaySinh);
            cmd.Parameters.AddWithValue("@NoiSinh", noiSinh);
            cmd.Parameters.AddWithValue("@HocHam", hocHam);
            cmd.Parameters.AddWithValue("@DaoTao", daoTao);
            cmd.Parameters.AddWithValue("@HiepHoi", hiepHoi);
            cmd.Parameters.AddWithValue("@AnPham", anPham);
            cmd.Parameters.AddWithValue("@CachThamGia", cachThamGia);

            int result = Convert.ToInt32(cmd.ExecuteScalar());

            if (result > 0)
            {
                int rs = InsertInfo(conn, trans, result, listChuyenMon, listCongViec, listLinhVuc, listTieuLinhVuc, listDuAn, listHocVi, listNgoaiNgu, listCongTac);
                if (rs == -1)
                {
                    return rs;
                }
            }

            trans.Commit();
            //int result = SqlHelper.ExecuteNonQuery(Common.Conn, "tblChuyenGia_Add", hoTenChuyenGia, phieuSo, ngayDangKy, gioiTinh, coQuan_TenChinhThuc, coQuan_TenGiaoDich, coQuan_DiaChi, coQuan_Tinh, coQuan_DienThoai, coQuan_Fax, coQuan_Email, nhaRieng_DiaChi, nhaRieng_DienThoai, nhaRieng_Email, ngaySinh, noiSinh, hocHam, daoTao, hiepHoi, anPham, cachThamGia);
            return result;
        }
        catch (Exception e)
        {
            if (trans != null)
            {
                trans.Rollback();
            }
            return -1;
        }
        finally
        {
            if (conn != null && conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
        }
	}


    public int Update(int idChuyenGia, String hoTenChuyenGia, int phieuSo, DateTime ngayDangKy, bool gioiTinh, String coQuan_TenChinhThuc, String coQuan_TenGiaoDich,
        String coQuan_DiaChi, String coQuan_Tinh, String coQuan_DienThoai, String coQuan_Fax, String coQuan_Email, String nhaRieng_DiaChi,
        String nhaRieng_DienThoai, String nhaRieng_Email, DateTime ngaySinh, String noiSinh, String hocHam, String daoTao, String hiepHoi,
        String anPham, byte cachThamGia, ArrayList listChuyenMon, ArrayList listCongViec, ArrayList listLinhVuc, ArrayList listTieuLinhVuc,
        DataTable listDuAn, DataTable listHocVi, DataTable listNgoaiNgu, DataTable listCongTac)
    {
        SqlConnection conn = null;
        SqlTransaction trans = null;
        try
        {
            conn = new SqlConnection(Common.Conn);
            conn.Open();
            trans = conn.BeginTransaction();

            SqlCommand cmd = new SqlCommand("tblChuyenGia_Update", conn, trans);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@IdChuyenGia", idChuyenGia);
            cmd.Parameters.AddWithValue("@HoTenChuyenGia", hoTenChuyenGia);
            cmd.Parameters.AddWithValue("@PhieuSo", phieuSo);
            cmd.Parameters.AddWithValue("@NgayDangKy", ngayDangKy);
            cmd.Parameters.AddWithValue("@GioiTinh", gioiTinh);
            cmd.Parameters.AddWithValue("@CoQuan_TenChinhThuc", coQuan_TenChinhThuc);
            cmd.Parameters.AddWithValue("@CoQuan_TenGiaoDich", coQuan_TenGiaoDich);
            cmd.Parameters.AddWithValue("@CoQuan_DiaChi", coQuan_DiaChi);
            cmd.Parameters.AddWithValue("@CoQuan_Tinh", coQuan_Tinh);
            cmd.Parameters.AddWithValue("@CoQuan_DienThoai", coQuan_DienThoai);
            cmd.Parameters.AddWithValue("@CoQuan_Fax", coQuan_Fax);
            cmd.Parameters.AddWithValue("@CoQuan_Email", coQuan_Email);
            cmd.Parameters.AddWithValue("@NhaRieng_DiaChi", nhaRieng_DiaChi);
            cmd.Parameters.AddWithValue("@NhaRieng_DienThoai", nhaRieng_DienThoai);
            cmd.Parameters.AddWithValue("@NhaRieng_Email", nhaRieng_Email);
            cmd.Parameters.AddWithValue("@NgaySinh", ngaySinh);
            cmd.Parameters.AddWithValue("@NoiSinh", noiSinh);
            cmd.Parameters.AddWithValue("@HocHam", hocHam);
            cmd.Parameters.AddWithValue("@DaoTao", daoTao);
            cmd.Parameters.AddWithValue("@HiepHoi", hiepHoi);
            cmd.Parameters.AddWithValue("@AnPham", anPham);
            cmd.Parameters.AddWithValue("@CachThamGia", cachThamGia);

            int result = Convert.ToInt32(cmd.ExecuteScalar());

            if (result > 0)
            {
                int rs = InsertInfo(conn, trans, result, listChuyenMon, listCongViec, listLinhVuc, listTieuLinhVuc, listDuAn, listHocVi, listNgoaiNgu, listCongTac);
                if (rs == -1)
                {
                    return rs;
                }
            }

            trans.Commit();
            //int result = SqlHelper.ExecuteNonQuery(Common.Conn, "tblChuyenGia_Add", hoTenChuyenGia, phieuSo, ngayDangKy, gioiTinh, coQuan_TenChinhThuc, coQuan_TenGiaoDich, coQuan_DiaChi, coQuan_Tinh, coQuan_DienThoai, coQuan_Fax, coQuan_Email, nhaRieng_DiaChi, nhaRieng_DienThoai, nhaRieng_Email, ngaySinh, noiSinh, hocHam, daoTao, hiepHoi, anPham, cachThamGia);
            return result;
        }
        catch (Exception e)
        {
            if (trans != null)
            {
                trans.Rollback();
            }
            return -1;
        }
        finally
        {
            if (conn != null && conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
        }
    }

    private int InsertInfo(SqlConnection conn, SqlTransaction trans, int result, ArrayList listChuyenMon, ArrayList listCongViec, ArrayList listLinhVuc, ArrayList listTieuLinhVuc,
        DataTable listDuAn, DataTable listHocVi, DataTable listNgoaiNgu, DataTable listCongTac) 
    {
        if (!InsertChuyenGia_DetailInList(conn, trans, "tblChuyenMon_AddRange", "@ListChuyenMon", result, listChuyenMon))
        {
            trans.Rollback();
            return -1;
        }

        if (!InsertChuyenGia_DetailInList(conn, trans, "tblLoaiCongViec_AddRange", "@ListCV", result, listCongViec))
        {
            trans.Rollback();
            return -1;
        }

        if (!InsertChuyenGia_DetailInList(conn, trans, "tblLinhVuc_AddRange", "@ListLinhVuc", result, listLinhVuc))
        {
            trans.Rollback();
            return -1;
        }

        if (!InsertChuyenGia_DetailInList(conn, trans, "tblTieuLinhVuc_AddRange", "@ListTieuLinhVuc", result, listTieuLinhVuc))
        {
            trans.Rollback();
            return -1;
        }

        if (!InsertDuAn(conn, trans, listDuAn, result))
        {
            trans.Rollback();
            return -1;
        }

        if (!InsertHocVi(conn, trans, listHocVi, result))
        {
            trans.Rollback();
            return -1;
        }

        if (!InsertNgoaiNgu(conn, trans, listNgoaiNgu, result))
        {
            trans.Rollback();
            return -1;
        }

        if (!InsertQTCT(conn, trans, listCongTac, result))
        {
            trans.Rollback();
            return -1;
        }

        return result;
    }
     
    private bool InsertDuAn(SqlConnection conn, SqlTransaction trans, DataTable dtDuAn, int idChuyenGia)
    {
        try
        {
            if (dtDuAn != null && dtDuAn.Rows.Count > 0)
            {
                //Delete cac du an hien tai
                SqlCommand cmdInsertChuyenMon = new SqlCommand("tblDuAn_DeleteByIdChuyenGia", conn, trans);
                cmdInsertChuyenMon.CommandType = CommandType.StoredProcedure;
                cmdInsertChuyenMon.Parameters.AddWithValue("@IdChuyenGia", idChuyenGia);
                cmdInsertChuyenMon.ExecuteNonQuery();

                //Insert cac du an hien tai
                cmdInsertChuyenMon.CommandText = "tblDuAn_Add";
                cmdInsertChuyenMon.Parameters.Clear();

                cmdInsertChuyenMon.Parameters.AddWithValue("@IdChuyenGia", idChuyenGia );
                cmdInsertChuyenMon.Parameters.Add("@TenDuAn", SqlDbType.NVarChar, 255, "TenDuAn");
                cmdInsertChuyenMon.Parameters.Add("@DiaDiem", SqlDbType.NVarChar, 255, "DiaDiem");
                cmdInsertChuyenMon.Parameters.Add("@TuThoiGian", SqlDbType.DateTime, 8, "TuThoiGian");
                cmdInsertChuyenMon.Parameters.Add("@DenThoiGian", SqlDbType.DateTime, 8, "DenThoiGian");
                cmdInsertChuyenMon.Parameters.Add("@NoiDungDuAn", SqlDbType.NVarChar, 2000, "NoiDungDuAn");
                cmdInsertChuyenMon.Parameters.Add("@CoQuan", SqlDbType.NVarChar, 255, "CoQuan");
                cmdInsertChuyenMon.Parameters.Add("@ViTri", SqlDbType.NVarChar, 255, "ViTri");
                cmdInsertChuyenMon.Parameters.Add("@TongThoiGian", SqlDbType.Int, 4, "TongThoiGian");
                cmdInsertChuyenMon.Parameters.Add("@CongViec", SqlDbType.NVarChar, 2000, "CongViec");

                SqlDataAdapter adap = new SqlDataAdapter();
                adap.InsertCommand = cmdInsertChuyenMon;
                adap.UpdateCommand = cmdInsertChuyenMon;
                foreach(DataRow row in dtDuAn.Rows)
                {
                    if(row.RowState == DataRowState.Unchanged)
                        row.SetAdded();
                }
                adap.Update(dtDuAn);
            }
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    private bool InsertHocVi(SqlConnection conn, SqlTransaction trans, DataTable dtHocVi, int idChuyenGia)
    {
        try
        {
            if (dtHocVi != null && dtHocVi.Rows.Count > 0)
            {
                //Delete cac du an hien tai
                SqlCommand cmdInsertChuyenMon = new SqlCommand("tblHocVi_DeleteByIdChuyenGia", conn, trans);
                cmdInsertChuyenMon.CommandType = CommandType.StoredProcedure;
                cmdInsertChuyenMon.Parameters.AddWithValue("@IdChuyenGia", idChuyenGia);
                cmdInsertChuyenMon.ExecuteNonQuery();

                //Insert cac du an hien tai
                cmdInsertChuyenMon.CommandText = "tblHocVi_Add";
                cmdInsertChuyenMon.Parameters.Clear();

                cmdInsertChuyenMon.Parameters.AddWithValue("@IdChuyenGia", idChuyenGia);
                cmdInsertChuyenMon.Parameters.Add("@TenHocVi", SqlDbType.NVarChar, 255, "TenHocVi");
                cmdInsertChuyenMon.Parameters.Add("@Mota", SqlDbType.NVarChar, 2000, "Mota");

                SqlDataAdapter adap = new SqlDataAdapter();
                adap.InsertCommand = cmdInsertChuyenMon;
                adap.UpdateCommand = cmdInsertChuyenMon;
                foreach (DataRow row in dtHocVi.Rows)
                {
                    if (row.RowState == DataRowState.Unchanged)
                        row.SetAdded();
                }
                adap.Update(dtHocVi);
            }
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    private bool InsertNgoaiNgu(SqlConnection conn, SqlTransaction trans, DataTable dtNgoaiNgu, int idChuyenGia) 
    {
        try
        {
            if (dtNgoaiNgu != null && dtNgoaiNgu.Rows.Count > 0)
            {
                //Delete cac du an hien tai
                SqlCommand cmdInsertChuyenMon = new SqlCommand("tblNgoaiNgu_DeleteByIdChuyenGia", conn, trans);
                cmdInsertChuyenMon.CommandType = CommandType.StoredProcedure;
                cmdInsertChuyenMon.Parameters.AddWithValue("@IdChuyenGia", idChuyenGia);
                cmdInsertChuyenMon.ExecuteNonQuery();

                //Insert cac du an hien tai
                cmdInsertChuyenMon.CommandText = "tblNgoaiNgu_Add";
                cmdInsertChuyenMon.Parameters.Clear();

                cmdInsertChuyenMon.Parameters.AddWithValue("@IdChuyenGia", idChuyenGia);
                cmdInsertChuyenMon.Parameters.Add("@TenNgoaiNgu", SqlDbType.NVarChar, 255, "TenNgoaiNgu");
                cmdInsertChuyenMon.Parameters.Add("@NgheNoi", SqlDbType.Char, 10, "NgheNoi");
                cmdInsertChuyenMon.Parameters.Add("@Doc", SqlDbType.Char, 10, "Doc");
                cmdInsertChuyenMon.Parameters.Add("@Viet", SqlDbType.Char, 10, "Viet");

                SqlDataAdapter adap = new SqlDataAdapter();
                adap.InsertCommand = cmdInsertChuyenMon;
                adap.UpdateCommand = cmdInsertChuyenMon;
                foreach (DataRow row in dtNgoaiNgu.Rows)
                {
                    if (row.RowState == DataRowState.Unchanged)
                        row.SetAdded();
                }
                adap.Update(dtNgoaiNgu);
            }
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    private bool InsertQTCT(SqlConnection conn, SqlTransaction trans, DataTable dtQTCT, int idChuyenGia)  
    {
        try
        {
            if (dtQTCT != null && dtQTCT.Rows.Count > 0)
            {
                //Delete cac du an hien tai
                SqlCommand cmdInsertChuyenMon = new SqlCommand("tblQuaTrinhCongTac_DeleteByIdChuyenGia", conn, trans);
                cmdInsertChuyenMon.CommandType = CommandType.StoredProcedure;
                cmdInsertChuyenMon.Parameters.AddWithValue("@IdChuyenGia", idChuyenGia);
                cmdInsertChuyenMon.ExecuteNonQuery();

                //Insert cac du an hien tai
                cmdInsertChuyenMon.CommandText = "tblQuaTrinhCongTac_Add";
                cmdInsertChuyenMon.Parameters.Clear();

                cmdInsertChuyenMon.Parameters.AddWithValue("@IdChuyenGia", idChuyenGia);
                cmdInsertChuyenMon.Parameters.Add("@TenCoQuan", SqlDbType.NVarChar, 255, "TenCoQuan");
                cmdInsertChuyenMon.Parameters.Add("@TuThoiGian", SqlDbType.DateTime, 8, "TuThoiGian");
                cmdInsertChuyenMon.Parameters.Add("@DenThoiGian", SqlDbType.DateTime, 8, "DenThoiGian");
                cmdInsertChuyenMon.Parameters.Add("@MoTaCongViec", SqlDbType.NVarChar, 2000, "MoTaCongViec");
                cmdInsertChuyenMon.Parameters.Add("@ViTri", SqlDbType.NVarChar, 2000, "ViTri");

                SqlDataAdapter adap = new SqlDataAdapter();
                adap.InsertCommand = cmdInsertChuyenMon;
                adap.UpdateCommand = cmdInsertChuyenMon;
                foreach (DataRow row in dtQTCT.Rows)
                {
                    if (row.RowState == DataRowState.Unchanged)
                        row.SetAdded();
                }
                adap.Update(dtQTCT);
            }
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    private bool InsertChuyenGia_DetailInList(SqlConnection conn, SqlTransaction trans, string commandName, string paramName, int idChuyenGia, ArrayList list)
    {
        try
        {
            if (list != null && list.Count > 0)
            {
                String strChuyenMon = "";
                foreach (int str in list)
                {
                    strChuyenMon += str.ToString() + ",";
                }
                strChuyenMon = strChuyenMon.Substring(0, strChuyenMon.Length - 1);
                SqlCommand cmdInsertChuyenMon = new SqlCommand(commandName, conn, trans);
                cmdInsertChuyenMon.CommandType = CommandType.StoredProcedure;
                cmdInsertChuyenMon.Parameters.AddWithValue("@IdChuyenGia", idChuyenGia);
                cmdInsertChuyenMon.Parameters.AddWithValue(paramName, strChuyenMon);
                cmdInsertChuyenMon.ExecuteNonQuery();
            }
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }

	public int Update(int idChuyenGia, String hoTenChuyenGia, int phieuSo, DateTime ngayDangKy, bool gioiTinh, String coQuan_TenChinhThuc, String coQuan_TenGiaoDich, String coQuan_DiaChi, String coQuan_Tinh, String coQuan_DienThoai, String coQuan_Fax, String coQuan_Email, String nhaRieng_DiaChi, String nhaRieng_DienThoai, String nhaRieng_Email, DateTime ngaySinh, String noiSinh, String hocHam, String daoTao, String hiepHoi, String anPham, byte cachThamGia)
	{
		try
		{
			int result = SqlHelper.ExecuteNonQuery(Common.Conn,"tblChuyenGia_Update",idChuyenGia,hoTenChuyenGia,phieuSo,ngayDangKy,gioiTinh,coQuan_TenChinhThuc,coQuan_TenGiaoDich,coQuan_DiaChi,coQuan_Tinh,coQuan_DienThoai,coQuan_Fax,coQuan_Email,nhaRieng_DiaChi,nhaRieng_DienThoai,nhaRieng_Email,ngaySinh,noiSinh,hocHam,daoTao,hiepHoi,anPham,cachThamGia);
			return result;
		}
		catch(Exception e) {return -1; }
	}

	public int Delete(int idChuyenGia)
	{
		try
		{
			int result = Convert.ToInt32(SqlHelper.ExecuteScalar(Common.Conn,"tblChuyenGia_Delete",idChuyenGia));
			return result;
		}
		catch(Exception e) {return -1; }
	}

	public int DeleteAll()
	{
		try
		{
			int result = SqlHelper.ExecuteNonQuery(Common.Conn,"tblChuyenGia_DeleteAll");
			return result;
		}
		catch(Exception e) {return -1; }
	}

	public DataSet Get(int idChuyenGia)
	{
		try
		{
			DataSet result = SqlHelper.ExecuteDataset(Common.Conn,"tblChuyenGia_Get",idChuyenGia);
			return result;
		}
		catch(Exception e) {return null; }
	}

	public DataSet GetAll()
	{
		try
		{
			DataSet result = SqlHelper.ExecuteDataset(Common.Conn,"tblChuyenGia_GetAll");
            result.Tables[0].TableName = "ChuyenGia";
            result.Tables[0].PrimaryKey = new DataColumn[] { result.Tables[0].Columns["IdChuyenGia"] };
            result.Tables[1].TableName = "DuAn";
            result.Tables[1].PrimaryKey = new DataColumn[] { result.Tables[1].Columns["IdDuAn"] };
            result.Tables[2].TableName = "HocVi";
            result.Tables[2].PrimaryKey = new DataColumn[] { result.Tables[2].Columns["IdHocVi"] };
            result.Tables[3].TableName = "NgoaiNgu";
            result.Tables[3].PrimaryKey = new DataColumn[] { result.Tables[3].Columns["IdNgoaiNgu"] };
            result.Tables[4].TableName = "QTCT";
            result.Tables[4].PrimaryKey = new DataColumn[] { result.Tables[4].Columns["IdCongTac"] };

            result.Tables[5].TableName = "CongViec";
            result.Tables[5].PrimaryKey = new DataColumn[] { result.Tables[5].Columns["IdLoaiCVChuyenGia"] };
            result.Tables[6].TableName = "ChuyenMon";
            result.Tables[6].PrimaryKey = new DataColumn[] { result.Tables[6].Columns["IdChuyenMonChuyenGia"] };
            result.Tables[7].TableName = "LinhVuc";
            result.Tables[7].PrimaryKey = new DataColumn[] { result.Tables[7].Columns["IdLinhVucChuyenGia"] };
            result.Tables[8].TableName = "TieuLinhVuc";
            result.Tables[8].PrimaryKey = new DataColumn[] { result.Tables[8].Columns["IdTieuLinhVucChuyenGia"] };

			return result;
		}
		catch(Exception e) {return null; }
	}

} 
}
